﻿using System;
using System.Data;
using System.Data.SqlClient;
using DataTools;

namespace BusinessLayer
{
    /// <summary>
    /// Business Layer Class for Horizon Airways
    /// </summary>
    public class BL : IDisposable
    {
        DataAccess da;

        /// <summary>
        /// Initializes a new instance of the Horizon Airways Business Layer
        /// </summary>
        public BL()
        {
            // TODO: Create the connection from a settings file
            da = new DataAccess(@".\HORIZON", "sa", "P@$$w0rd", "horizon");
        }

        /// <summary>
        /// Gets the passenger with the given PNR number.
        /// </summary>
        /// <param name="PNR">PNR number.</param>
        /// <returns>A DataTable with the passenger information.</returns>
        public DataTable GetPassenger(int pnr)
        {
            DataTable dt = new DataTable();

            try
            {
                StoreProcedure sp = new StoreProcedure("spGetPassenger");
                sp.AddParameter("pnr", SqlDbType.Int, 4, pnr);

                dt = da.ExecuteStoreProcedure(sp);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return dt;
        }

        public DataTable GetPassengers()
        {
            DataTable dt = new DataTable();

            try
            {
                StoreProcedure sp = new StoreProcedure("spGetPassenger");
                sp.AddParameter("pnr", SqlDbType.Int, 4, 0);

                dt = da.ExecuteStoreProcedure(sp);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return dt;
        }

        public int GetWaitListCount(string flightNo)
        {
            int waitListCount = 0;

            try
            {
                StoreProcedure sp = new StoreProcedure("spGetWaitListCount");
                sp.AddParameter("flightNo", SqlDbType.Char, 6, flightNo);

                waitListCount = int.Parse(da.ExecuteStoreProcedure(sp).Rows[0][0].ToString());
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return waitListCount;
        }

        public bool Reserve(int pnr, string flightNo, string fName, string lName, int age, char gender, char seatClass, string seatPref, string mealPref, string ssr, DateTime date)
        {
            bool flag = false;
            try
            {
                StoreProcedure sp = new StoreProcedure("spReservation");
                sp.AddParameter("PNRNo", SqlDbType.Int, 4, pnr);
                sp.AddParameter("FlightNo", SqlDbType.Char, 5, flightNo);
                sp.AddParameter("FName", SqlDbType.VarChar, 15, fName);
                sp.AddParameter("LName", SqlDbType.VarChar, 15, lName);
                sp.AddParameter("Age", SqlDbType.Int, 4, age);
                sp.AddParameter("Gender", SqlDbType.Char, 1, gender);
                sp.AddParameter("Class", SqlDbType.Char, 1, seatClass);
                sp.AddParameter("SeatPref", SqlDbType.Char, 1, seatPref);
                sp.AddParameter("MealPref", SqlDbType.VarChar, 20, mealPref);
                sp.AddParameter("SSR", SqlDbType.Char, 1, ssr);
                sp.AddParameter("Date", SqlDbType.Date, 8, date);

                if (da.ExecuteStoreProcedureNonQuery(sp) > 0)
                    flag = true;

            }
            catch (SqlException ex)
            {   
                throw ex;
            }
            return flag;
        } 

        public DataTable GetFlights(DateTime date, string sector)
        {
            DataTable dt = new DataTable();
            try
            {
                StoreProcedure sp = new StoreProcedure("spFindFlights");
                sp.AddParameter("date", SqlDbType.DateTime, 8, date);
                sp.AddParameter("sector", SqlDbType.Char, 5, sector);

                dt = da.ExecuteStoreProcedure(sp);
            }
            catch (SqlException ex)
            {
                
                throw ex;
            }
            return dt;
        }

        public DataTable GetFlights(string sector)
        {
            DataTable dt = new DataTable();
            try
            {
                StoreProcedure sp = new StoreProcedure("spFindFlightsBySector");
                sp.AddParameter("sector", SqlDbType.Char, 5, sector);

                dt = da.ExecuteStoreProcedure(sp);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable GetDailyReport(DateTime date)
        {
            DataTable dt = new DataTable();

            try
            {
                StoreProcedure sp = new StoreProcedure("spGetDailyReport");
                sp.AddParameter("date", SqlDbType.DateTime, 8, date);

                dt = da.ExecuteStoreProcedure(sp);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return dt;
        }

        public DataTable GetPassengersByStatus(char status, string flightNo)
        {
            DataTable dt = new DataTable();

            try
            {
                StoreProcedure sp = new StoreProcedure("spGetPassengersByStatus");
                sp.AddParameter("status", SqlDbType.Int, 4, status);
                sp.AddParameter("flightNo", SqlDbType.Char, 6, flightNo);

                dt = da.ExecuteStoreProcedure(sp);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return dt;
        }

        public decimal CalculateRefund(int pnr)
        {
            decimal refund = 0;

            try
            {
                StoreProcedure sp = new StoreProcedure("spCalculateRefund");
                sp.AddParameter("pnr", SqlDbType.Int, 4, pnr);

                refund = decimal.Parse(da.ExecuteStoreProcedure(sp).Rows[0][0].ToString());
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return refund;
        }

        public bool CancelPassenger(int pnr, decimal refund)
        {
            bool value = false;

            try
            {
                StoreProcedure sp = new StoreProcedure("spCancelPassenger");
                sp.AddParameter("pnr", SqlDbType.Int, 4, pnr);
                sp.AddParameter("refund", SqlDbType.Money,8,refund);

                refund = decimal.Parse(da.ExecuteStoreProcedure(sp).Rows[0][0].ToString());
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return value;
        }

        public bool AddWaitList(int pnr, string flightNo, string fName, string lName, int age, char gender, char seatClass, string seatPref, string mealPref, string ssr)
        {
            bool flag = false;

            try
            {
                StoreProcedure sp = new StoreProcedure("spAddWaitList");
                sp.AddParameter("PNRNo", SqlDbType.Int, 4, pnr);
                sp.AddParameter("FlightNo", SqlDbType.Char, 5, flightNo);
                sp.AddParameter("FName", SqlDbType.VarChar, 15, fName);
                sp.AddParameter("LName", SqlDbType.VarChar, 15, lName);
                sp.AddParameter("Age", SqlDbType.Int, 4, age);
                sp.AddParameter("Gender", SqlDbType.Char, 1, gender);
                sp.AddParameter("Class", SqlDbType.Char, 1, seatClass);
                sp.AddParameter("SeatPref", SqlDbType.Char, 1, seatPref);
                sp.AddParameter("MealPref", SqlDbType.VarChar, 20, mealPref);
                sp.AddParameter("SSR", SqlDbType.Char, 1, ssr);

                if (da.ExecuteStoreProcedureNonQuery(sp) > 0)
                    flag = true;

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return flag;

        }

        #region IDisposable Members

        private bool disposed = false;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (da != null)
                {
                    da.Dispose();
                    da = null;
                }
            }

            disposed = true;
        }

        #endregion
    }
}
